﻿CREATE procedure sh_Archive_MakeView
	@Object varchar(128) = 'v_FaxToDealer'
AS

DECLARE @ColName varchar(128),@ColNames varchar(8000),@ColIndex varchar(3),@ColSelect varchar(8000),
				@SQL varchar(8000)

DECLARE @CurCols CURSOR

EXEC sec__GetColumnList @Object,@CurCols OUTPUT

FETCH NEXT FROM @CurCols INTO @ColIndex,@ColName

SELECT @ColNames = '',@ColSelect = ''

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @ColNames = @ColNames + 'C'+@ColIndex+'.CV AS ['+@ColName+'],'

	SET @ColSelect = @ColSelect + '
LEFT OUTER JOIN
(SELECT HID, CV
FROM   thist_Child
WHERE CN = '''+@ColName+''') AS C'+@ColIndex+' ON R.HID = C'+@ColIndex+'.HID
'

	FETCH NEXT FROM @CurCols INTO @ColIndex,@ColName

END
CLOSE @CurCols
DEALLOCATE @CurCols

if exists (select * from dbo.sysobjects where id = object_id(N'vhist_'+@Object) and OBJECTPROPERTY(id, N'IsView') = 1)
EXEC ('drop view vhist_'+@Object)

SET @SQL = '
CREATE VIEW vhist_'+@Object+'
AS 
SELECT '+@ColNames+'R.HID, R.UserName Hist_UserName, R.DateStamp Hist_DateStamp, R.ObjectName Hist_ObjectName,R.PK_1,R.PK_2
FROM  thist_Root R '+@ColSelect+'
WHERE ObjectName='''+@Object+''''

EXEC (@SQL)
